SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>SQLAlchemy</title>

</head>

SQLAlchemy

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .infobox-subbox{padding:0;border:none;margin:-3px;width:auto;min-width:100%;font-size:100%;clear:none;float:none;background-color:transparent}.mw-parser-output .infobox-3cols-child{margin:auto}.mw-parser-output .infobox .navbar{font-size:100%}@media screen{html.skin-theme-clientpref-night .mw-parser-output .infobox-full-data:not(.notheme)>div:not(.notheme)[style]{background:#1f1f23!important;color:#f8f9fa}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .infobox-full-data:not(.notheme)>div:not(.notheme)[style]{background:#1f1f23!important;color:#f8f9fa}}@media(min-width:640px){body.skin--responsive .mw-parser-output .infobox-table{display:table!important}body.skin--responsive .mw-parser-output .infobox-table>caption{display:table-caption!important}body.skin--responsive .mw-parser-output .infobox-table>tbody{display:table-row-group}body.skin--responsive .mw-parser-output .infobox-table th,body.skin--responsive .mw-parser-output .infobox-table td{padding-left:inherit;padding-right:inherit}}

/* end https://en.wikipedia.org/ */

</style><table class="infobox vevent"><tbody><tr><td colspan="2" class="infobox-image logo"><div class="infobox-caption">Abbreviated SQLAlchemy Logo</div></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;"><a href="Programmer" title="Programmer">Original author(s)</a></th><td class="infobox-data">Michael Bayer<a href="#cite_note-1">[1]</a></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;">Initial release</th><td class="infobox-data">February 14, 2006 (2006-02-14)<a href="#cite_note-releases-2">[2]</a></td></tr><tr style="display: none;"><td colspan="2" class="infobox-full-data"></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;"><a href="Software_release_life_cycle" title="Software release life cycle">Stable release</a></th><td class="infobox-data"><div style="margin:0px;">2.0.41<a href="#cite_note-wikidata-9986a6598bd01936424ce70755decbbe4c38ced6-v20-3">[3]</a>

/ 14 May 2025 (14 May 2025)</div></td></tr><tr style="display:none"><td colspan="2">

</td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;"><a href="Repository_(version_control)" title="Repository (version control)">Repository</a></th><td class="infobox-data"><style data-mw-deduplicate="TemplateStyles:r1126788409">

/* start https://en.wikipedia.org/ */

.mw-parser-output .plainlist ol,.mw-parser-output .plainlist ul{line-height:inherit;list-style:none;margin:0;padding:0}.mw-parser-output .plainlist ol li,.mw-parser-output .plainlist ul li{margin-bottom:0}

/* end https://en.wikipedia.org/ */

</style><div class="plainlist"><ul><li><a rel="nofollow" class="external text" href="https://github.com/sqlalchemy/sqlalchemy">github.com/sqlalchemy/sqlalchemy</a> </li></ul>

</div></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;">Written in</th><td class="infobox-data"><a href="Python_(programming_language)" title="Python (programming language)">Python</a></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;"><a href="Operating_system" title="Operating system">Operating system</a></th><td class="infobox-data"><a href="Cross-platform" class="mw-redirect" title="Cross-platform">Cross-platform</a></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;"><a href="Software_categories#Categorization_approaches" title="Software categories">Type</a></th><td class="infobox-data"><a href="Object-relational_mapping" class="mw-redirect" title="Object-relational mapping">Object-relational mapping</a></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;"><a href="Software_license" title="Software license">License</a></th><td class="infobox-data"><a href="MIT_License" title="MIT License">MIT License</a><a href="#cite_note-license-bitbucket-4">[4]</a></td></tr><tr><th scope="row" class="infobox-label" style="white-space: nowrap;">Website</th><td class="infobox-data"><a rel="nofollow" class="external text" href="https://www.sqlalchemy.org/">www.sqlalchemy.org</a> </td></tr></tbody></table>

SQLAlchemy is an <a href="Open_source" title="Open source">open-source</a> <a href="Python_(programming_language)" title="Python (programming language)">Python</a> library that provides an <a href="SQL" title="SQL">SQL</a> toolkit (called "SQLAlchemy Core") and an <a href="Object%E2%80%93relational_mapping" title="Object–relational mapping">object–relational mapper</a> (ORM) for database interactions. It allows developers to work with databases using Python objects, enabling efficient and flexible database access.

<div class="mw-heading mw-heading2"><h2 id="Description">Description</h2></div>

SQLAlchemy offers tools for <a href="Database_schema" title="Database schema">database schema</a> generation, querying, and object-relational mapping. Key features include:

<ul><li>A comprehensive embedded <a href="Domain-specific_language" title="Domain-specific language">domain-specific language</a> for SQL in Python called "SQLAlchemy Core" that provides means to construct and execute SQL queries.</li>

<li>A powerful ORM that allows the mapping of Python classes to database tables.</li>

<li>Support for database schema migrations.</li>

<li>Compatibility with multiple database backends.</li>

<li>Tools for <a href="Database_connection" title="Database connection">database connection</a> pooling and transaction management.</li></ul>

<div class="mw-heading mw-heading2"><h2 id="History">History</h2></div>

SQLAlchemy was first released in February 2006. It has evolved to include a wide range of features for database interaction and has gained popularity among Python developers. Notable versions include:

<ul><li>Version 0.1 (2006):<a href="#cite_note-5">[5]</a> Initial release.</li>

<li>Version 1.0 (2015):<a href="#cite_note-6">[6]</a> Major enhancements in ORM and SQL expression language.</li>

<li>Version 1.4 (2021):<a href="#cite_note-7">[7]</a> Introduction of a new ORM <a href="API" title="API">API</a>.</li></ul>

<div class="mw-heading mw-heading2"><h2 id="Example">Example</h2></div>

The following example represents an n-to-1 relationship between movies and their directors. It is shown how user-defined Python classes create corresponding database tables, how instances with relationships are created from either side of the relationship, and finally how the data can be queried — illustrating automatically generated SQL queries for both <a href="Lazy_loading" title="Lazy loading">lazy</a> and eager loading.

<div class="mw-heading mw-heading3"><h3 id="Schema_definition">Schema definition</h3></div>

Creating two Python classes and corresponding database tables in the DBMS:

<div class="mw-highlight mw-highlight-lang-python mw-content-ltr" dir="ltr"><pre>from sqlalchemy import *

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relation, sessionmaker

Base = declarative_base()

class Movie(Base):

__tablename__ = "movies"

id = Column(Integer, primary_key=True)

title = Column(String(255), nullable=False)

year = Column(Integer)

directed_by = Column(Integer, ForeignKey("directors.id"))

director = relation("Director", backref="movies", lazy=False)

def __init__(self, title=None, year=None):

self.title = title

self.year = year

def __repr__(self):

return f"Movie({self.title}, {self.year}, {self.director})"

class Director(Base):

__tablename__ = "directors"

name = Column(String(50), nullable=False, unique=True)

def __init__(self, name=None):

self.name = name

def __repr__(self):

return f"Director({self.name})"

engine = create_engine("dbms://user:pwd@host/dbname")

Base.metadata.create_all(engine)

</pre></div>

<div class="mw-heading mw-heading3"><h3 id="Data_insertion">Data insertion</h3></div>

One can insert a director-movie relationship via either entity:

<div class="mw-highlight mw-highlight-lang-python mw-content-ltr" dir="ltr"><pre>Session = sessionmaker(bind=engine)

session = Session()

m1 = Movie("Robocop", 1987)

m1.director = Director("Paul Verhoeven")

d2 = Director("George Lucas")

d2.movies = [Movie("Star Wars", 1977), Movie("THX 1138", 1971)]

try:

session.add(m1)

session.add(d2)

session.commit()

except:

session.rollback()

</pre></div>

<div class="mw-heading mw-heading3"><h3 id="Querying">Querying</h3></div>

<div class="mw-highlight mw-highlight-lang-python mw-content-ltr" dir="ltr"><pre>alldata = session.query(Movie).all()

for somedata in alldata:

print(somedata)

</pre></div>

SQLAlchemy issues the following query to the DBMS (omitting aliases):

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT movies.id, movies.title, movies.year, movies.directed_by, directors.id, directors.name

FROM movies LEFT OUTER JOIN directors ON directors.id = movies.directed_by

</pre></div>

The output:

<div class="mw-highlight mw-highlight-lang-python mw-content-ltr" dir="ltr"><pre>Movie('Robocop', 1987L, Director('Paul Verhoeven'))

Movie('Star Wars', 1977L, Director('George Lucas'))

Movie('THX 1138', 1971L, Director('George Lucas'))

</pre></div>

Setting <code>lazy=True</code> (default) instead, SQLAlchemy would first issue a query to get the list of movies and only when needed (lazy) for each director a query to get the name of the corresponding director:

FROM movies

SELECT directors.id, directors.name

FROM directors

WHERE directors.id = %s

</pre></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .portalbox{padding:0;margin:0.5em 0;display:table;box-sizing:border-box;max-width:175px;list-style:none}.mw-parser-output .portalborder{border:1px solid var(--border-color-base,#a2a9b1);padding:0.1em;background:var(--background-color-neutral-subtle,#f8f9fa)}.mw-parser-output .portalbox-entry{display:table-row;font-size:85%;line-height:110%;height:1.9em;font-style:italic;font-weight:bold}.mw-parser-output .portalbox-image{display:table-cell;padding:0.2em;vertical-align:middle;text-align:center}.mw-parser-output .portalbox-link{display:table-cell;padding:0.2em 0.2em 0.2em 0.3em;vertical-align:middle}@media(min-width:720px){.mw-parser-output .portalleft{margin:0.5em 1em 0.5em 0}.mw-parser-output .portalright{clear:right;float:right;margin:0.5em 0 0.5em 1em}}

/* end https://en.wikipedia.org/ */

</style>

<ul><li><a href="SQLObject" title="SQLObject">SQLObject</a></li>

<li><a href="Storm_(software)" title="Storm (software)">Storm</a></li>

<li><a href="Pylons_project" title="Pylons project">Pylons</a></li>

<li><a href="TurboGears" title="TurboGears">TurboGears</a></li>

<li><a href="Cubes_(OLAP_server)" title="Cubes (OLAP server)">Cubes (OLAP server)</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

<li id="cite_note-1"><a href="#cite_ref-1">^</a> <a rel="nofollow" class="external text" href="http://techspot.zzzeek.org/">Mike Bayer is the creator of SQLAlchemy and Mako Templates for Python.</a>

</li>

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("./mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("./mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("./mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("./mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.sqlalchemy.org/download.html">"Download - SQLAlchemy"</a>. SQLAlchemy. Retrieved 21 February 2015.</cite>

</li>

<li id="cite_note-wikidata-9986a6598bd01936424ce70755decbbe4c38ced6-v20-3"><a href="#cite_ref-wikidata-9986a6598bd01936424ce70755decbbe4c38ced6-v20_3-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://github.com/sqlalchemy/sqlalchemy/releases/tag/rel_2_0_41">"Release 2.0.41"</a>. 14 May 2025. Retrieved 1 June 2025.</cite>

</li>

<li id="cite_note-license-bitbucket-4"><a href="#cite_ref-license-bitbucket_4-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://bitbucket.org/zzzeek/sqlalchemy/src/775ff3419fa03ddbb1b4b6d7350344b69df461ef/LICENSE">"zzzeek / sqlalchemy / source / LICENSE"</a>. BitBucket. Retrieved 21 February 2015.</cite>

</li>

<li id="cite_note-5"><a href="#cite_ref-5">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.sqlalchemy.org/en/20/changelog/changelog_01.html">"0.1 Changelog — SQLAlchemy 2.0 Documentation"</a>. docs.sqlalchemy.org. Retrieved 2024-07-04.</cite>

</li>

<li id="cite_note-6"><a href="#cite_ref-6">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.sqlalchemy.org/en/20/changelog/changelog_10.html#change-1.0.0">"1.0 Changelog — SQLAlchemy 2.0 Documentation"</a>. docs.sqlalchemy.org. Retrieved 2024-07-04.</cite>

</li>

<li id="cite_note-7"><a href="#cite_ref-7">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.sqlalchemy.org/en/20/changelog/changelog_14.html#change-1.4.0">"1.4 Changelog — SQLAlchemy 2.0 Documentation"</a>. docs.sqlalchemy.org. Retrieved 2024-07-04.</cite>

</li>

</ol></div></div>

<dl><dt>Notes</dt></dl>

<ul><li><cite id="CITEREFGift2008" class="citation web cs1">Gift, Noah (12 Aug 2008). <a rel="nofollow" class="external text" href="https://www.ibm.com/developerworks/aix/library/au-sqlalchemy/">"Using SQLAlchemy"</a>. Developerworks. IBM. Retrieved 8 Feb 2011.</cite></li>

<li>Rick Copeland, Essential SQLAlchemy, <a href="O'Reilly_Media" title="O'Reilly Media">O'Reilly</a>, 2008, <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>0-596-51614-2</bdi></li></ul></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-06-09" href="https://en.wikipedia.org/wiki/?title=SQLAlchemy&oldid=1294724957">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>